# -*- coding: utf-8 -*-
# scripts/dm_service.py
# @author zoulei
# 达梦数据库 ODBC Python 服务，供 Node 端 HTTP 调用

import sys
import os

# 设置标准输出和错误输出的编码为UTF-8
if sys.platform.startswith('win'):
    # Windows系统下设置控制台编码
    import codecs
    sys.stdout = codecs.getwriter('utf-8')(sys.stdout.detach())
    sys.stderr = codecs.getwriter('utf-8')(sys.stderr.detach())
    # 设置环境变量
    os.environ['PYTHONIOENCODING'] = 'utf-8'

from fastapi import FastAPI
from pydantic import BaseModel
import uvicorn
import pyodbc
import threading
import signal
import atexit
import time
from fastapi import Request
from typing import Optional
from fastapi import Query
import re

app = FastAPI()

# 全局变量
conn = None
conn_lock = threading.Lock()

# 清理函数
# 关闭全局数据库连接，释放资源
# @author zoulei
def cleanup():
    global conn
    if conn:
        try:
            conn.close()
            print("数据库连接已关闭", file=sys.stderr)
        except Exception as e:
            print(f"关闭数据库连接时出错: {e}", file=sys.stderr)
        conn = None

# 信号处理函数
# 捕获退出信号，自动清理资源
# @author zoulei
def signal_handler(signum, frame):
    print(f"收到信号 {signum}，正在清理资源...", file=sys.stderr)
    cleanup()
    sys.exit(0)

# 注册信号处理器
signal.signal(signal.SIGINT, signal_handler)
signal.signal(signal.SIGTERM, signal_handler)

# 注册退出时的清理函数
atexit.register(cleanup)

# 添加环境变量调试信息
print("=== Python服务环境变量调试信息 ===", file=sys.stderr)
print("DM_HOST:", os.environ.get("DM_HOST"), file=sys.stderr)
print("DM_USERNAME:", os.environ.get("DM_USERNAME"), file=sys.stderr)
print("DM_PASSWORD:", os.environ.get("DM_PASSWORD"), file=sys.stderr)
print("DM_HOME:", os.environ.get("DM_HOME"), file=sys.stderr)
print("==================================", file=sys.stderr)

# 全局数据库配置
DM_DRIVER = "DM8 ODBC DRIVER"
db_config = {
    "host": os.environ.get("DM_HOST", "<your_host>:<your_port>"),
    "username": os.environ.get("DM_USERNAME", "<your_uid>"),
    "password": os.environ.get("DM_PASSWORD", "<your_pwd>"),
    "DM_HOME": os.environ.get("DM_HOME", "<your_dm_home>")
}

print("=== 初始数据库配置 ===", file=sys.stderr)
print("db_config:", db_config, file=sys.stderr)
print("=====================", file=sys.stderr)

@app.post("/config")
async def update_config(request: Request):
    data = await request.json()
    print(data, file=sys.stderr)
    db_config.update(data)
    return {"status": "ok", "config": db_config}

# get_conn() 里用 db_config 里的参数拼接连接字符串
# 获取数据库连接，自动重连，线程安全
# @author zoulei
def get_conn():
    global conn
    with conn_lock:
        try:
            conn_str = (
                f"DRIVER={{{DM_DRIVER}}};"
                f"SERVER={db_config['host']};"
                f"UID={db_config['username']};"
                f"PWD={db_config['password']};"
                "ServerType=8;"
                "Protocol=TCP;"
            )
            # 如果连接不存在或已断开，创建新连接
            if conn is None or not conn_connected(conn):
                if conn is not None:
                    try:
                        conn.close()
                        print("关闭旧的数据库连接", file=sys.stderr)
                    except Exception as e:
                        print(f"关闭旧连接时出错: {e}", file=sys.stderr)
                print(f"正在连接数据库: {db_config['host']}", file=sys.stderr)
                conn = pyodbc.connect(conn_str, autocommit=True)
                print("数据库连接成功", file=sys.stderr)
            return conn
        except Exception as e:
            print(f"数据库连接失败: {e}", file=sys.stderr)
            conn = None
            raise

# 检查数据库连接是否可用
# @author zoulei
def conn_connected(c):
    try:
        if c is None:
            return False
        c.cursor().execute("SELECT 1")
        return True
    except Exception as e:
        print(f"检查连接状态失败: {e}", file=sys.stderr)
        return False

class SQLModel(BaseModel):
    sql: str
    where: Optional[str] = None

class TableInfoModel(BaseModel):
    tableName: str
    schema_name: str = None

class CodeValueQueryModel(BaseModel):
    codetype: Optional[str] = None
    codename: Optional[str] = None
    codevalue: Optional[str] = None
    parent: Optional[str] = None
    page: int = 1  # 分页参数，默认第1页 @author zoulei

# ping接口，检测服务可用性
# @author zoulei
@app.get("/ping")
def ping():
    try:
        get_conn()
        return {"status": "ok"}
    except Exception as e:
        return {"status": "fail", "message": str(e)}

# SQL查询接口
# @author zoulei
@app.post("/query")
def query(data: SQLModel):
    # 禁止包含危险SQL关键字
    if re.search(r"\b(delete|drop|create|update|insert|alter|truncate|replace)\b", data.sql, re.I):
        return {"rows": [], "columns": [], "rowCount": 0, "message": "禁止包含危险SQL关键字: delete/drop/create/update/insert/alter/truncate/replace"}
 
    try:
        c = get_conn().cursor()
        c.execute(data.sql)
        columns = [desc[0] for desc in c.description]
        rows = [dict(zip(columns, row)) for row in c.fetchall()]
        return {
            "rows": rows,
            "columns": columns,
            "rowCount": len(rows)
        }
    except Exception as e:
        return {"rows": [], "columns": [], "rowCount": 0, "message": str(e)}

# SQL更新接口
# @author zoulei
@app.post("/execute")
def execute(data: SQLModel):
    # 禁止执行drop、truncate等危险操作
    if re.search(r"\b(drop|truncate)\b", data.sql, re.I):
        return {"affectedRows": 0, "message": "禁止执行DROP/TRUNCATE等危险操作"}
    # 如果是delete语句，必须有where条件
    sql_strip = data.sql.strip()
    if sql_strip[:6].lower() == "delete":
        # 检查是否有where
        if re.search(r"\bwhere\b", sql_strip, re.I):
            sql_to_exec = sql_strip
        elif data.where:
            # 自动拼接where条件
            if sql_strip.endswith(";"):
                sql_to_exec = sql_strip[:-1] + " WHERE " + data.where + ";"
            else:
                sql_to_exec = sql_strip + " WHERE " + data.where
        else:
            return {"affectedRows": 0, "message": "DELETE语句必须包含WHERE条件，或通过参数提供where条件"}
        try:
            c = get_conn().cursor()
            result = c.execute(sql_to_exec)
            affected = result.rowcount
            return {"affectedRows": affected}
        except Exception as e:
            return {"affectedRows": 0, "message": str(e)}
    # 其他SQL
    try:
        c = get_conn().cursor()
        result = c.execute(data.sql)
        affected = result.rowcount
        return {"affectedRows": affected}
    except Exception as e:
        return {"affectedRows": 0, "message": str(e)}

# 获取所有表名接口
# @author zoulei
@app.get("/tables")
def tables():
    try:
        c = get_conn().cursor()
        c.execute("SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME")
        rows = [row[0] for row in c.fetchall()]
        return rows
    except Exception as e:
        return {"rows": [], "message": str(e)}

# 获取表结构详细信息接口
# @author zoulei
@app.post("/table_info")
def table_info(data: TableInfoModel):
    try:
        table = data.tableName.upper()
        owner_cond = f"OWNER = '{data.schema_name}' AND " if data.schema_name else ""
        c = get_conn().cursor()
        # 列信息
        col_sql = f"""
            SELECT c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.NULLABLE, c.DATA_DEFAULT, cc.COMMENTS AS COL_COMMENT
            FROM ALL_TAB_COLUMNS c
            LEFT JOIN ALL_COL_COMMENTS cc
              ON c.OWNER = cc.OWNER AND c.TABLE_NAME = cc.TABLE_NAME AND c.COLUMN_NAME = cc.COLUMN_NAME
            WHERE {owner_cond}c.TABLE_NAME = '{table}'
            ORDER BY c.COLUMN_ID
        """
        c.execute(col_sql)
        columns = []
        for row in c.fetchall():
            columns.append({
                "name": row[0],
                "type": row[1],
                "length": row[2],
                "nullable": row[3] == 'Y',
                "defaultValue": row[4],
                "comment": row[5]
            })
        # 表备注
        tab_comment_sql = f"SELECT COMMENTS FROM ALL_TAB_COMMENTS WHERE {owner_cond}TABLE_NAME = '{table}'"
        c.execute(tab_comment_sql)
        tab_comment = ""
        res = c.fetchall()
        if res:
            tab_comment = res[0][0]
        # 索引
        idx_sql = f"""
            SELECT i.INDEX_NAME, i.UNIQUENESS, ic.COLUMN_NAME
            FROM ALL_INDEXES i
            LEFT JOIN ALL_IND_COLUMNS ic
              ON i.OWNER = ic.INDEX_OWNER AND i.INDEX_NAME = ic.INDEX_NAME
            WHERE {owner_cond}i.TABLE_NAME = '{table}'
            ORDER BY i.INDEX_NAME, ic.COLUMN_POSITION
        """
        c.execute(idx_sql)
        idx_map = {}
        for row in c.fetchall():
            idx_name = row[0]
            if idx_name not in idx_map:
                idx_map[idx_name] = {
                    "name": idx_name,
                    "columns": [],
                    "type": "UNIQUE" if row[1] == "UNIQUE" else "NORMAL",
                    "unique": row[1] == "UNIQUE"
                }
            if row[2]:
                idx_map[idx_name]["columns"].append(row[2])
        indexes = list(idx_map.values())
        return {
            "name": data.tableName,
            "columns": columns,
            "indexes": indexes,
            "comment": tab_comment
        }
    except Exception as e:
        return {"columns": [], "indexes": [], "comment": "", "message": str(e)}

# code_value二级代码组合查询接口
# 支持codetype、codename、codevalue、parent任意组合，至少一个条件，支持分页
# @author zoulei
@app.post("/code_value_query")
def code_value_query(data: CodeValueQueryModel):
    # 校验至少有一个入参
    if not (data.codetype or data.codename or data.codevalue or data.parent):
        return {"rows": [], "columns": [], "rowCount": 0, "message": "至少需要一个查询条件"}
    try:
        page_size = 1000
        page = data.page if data.page and data.page > 0 else 1
        offset = (page - 1) * page_size
        sql = "SELECT CODE_TYPE, CODE_VALUE, CODE_NAME, SUB_CODE_VALUE FROM code_value WHERE 1=1"
        params = []
        if data.codetype:
            sql += " AND CODE_TYPE = ?"
            params.append(data.codetype.upper())
        if data.codename:
            sql += " AND CODE_NAME LIKE ?"
            params.append(f"%{data.codename}%")
        if data.codevalue:
            sql += " AND CODE_VALUE = ?"
            params.append(data.codevalue)
        if data.parent:
            sql += " AND SUB_CODE_VALUE = ?"
            params.append(data.parent)
        sql += " ORDER BY ININO LIMIT ? OFFSET ?"
        params.extend([page_size + 1, offset])  # 多查一条判断是否还有下一页
        c = get_conn().cursor()
        c.execute(sql, params)
        columns = [desc[0] for desc in c.description]
        rows = [dict(zip(columns, row)) for row in c.fetchmany(page_size + 1)]
        has_more = len(rows) > page_size
        rows = rows[:page_size]
        return {
            "rows": rows,
            "columns": columns,
            "rowCount": len(rows),
            "page": page,
            "pageSize": page_size,
            "hasMore": has_more
        }
    except Exception as e:
        return {"rows": [], "columns": [], "rowCount": 0, "message": str(e)}

# 关闭服务接口，释放资源并退出进程
# @author zoulei
@app.post("/shutdown")
def shutdown():
    """关闭Python服务"""
    print("收到关闭请求，正在清理资源...", file=sys.stderr)
    cleanup()
    # 启动一个线程来关闭服务器
    import threading
    def shutdown_server():
        import os
        os._exit(0)
    threading.Timer(1.0, shutdown_server).start()
    return {"status": "shutting_down"}

if __name__ == '__main__':
    print("sys.executable:", sys.executable, file=sys.stderr)
    print("Python服务启动中...", file=sys.stderr)
    
    # 检查端口是否被占用
    import socket
    try:
        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
        sock.settimeout(1)
        result = sock.connect_ex(('127.0.0.1', 5001))
        sock.close()
        
        if result == 0:
            print("警告: 端口5001已被占用，可能存在残留的Python服务", file=sys.stderr)
            print("建议先关闭已有的服务再启动", file=sys.stderr)
            # 可以选择退出或继续
            # sys.exit(1)
    except Exception as e:
        print(f"检查端口时出错: {e}", file=sys.stderr)
    
    uvicorn.run(app, host="127.0.0.1", port=5001, log_level="info", access_log=True) 